package com.liwen.kefu.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: Liwen
 * @createTime: 2024/11/1
 * @description:
 */
public class ExcelUtil {


    /**
     * 读取excel，返回list
     * @param excelPath
     * @return
     * @throws IOException
     */
    public static List<String> readExcelSqlData(String excelPath) throws IOException {
        File file = new File(excelPath);
        FileInputStream inputStream = new FileInputStream(file);
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        List<String> list = new ArrayList<>();
        StringBuffer titleBuffer = new StringBuffer();
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            StringBuffer rowBuffer = new StringBuffer();
            if (null != row) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (j >= row.getLastCellNum()) {
                        break;
                    }
                    if (i == 0) {
                        titleBuffer.append("`" + cell + "`" + ",");
                        continue;
                    }
                    if (cell == null) {
                        cell = row.createCell(j);
                        cell.setCellValue("NULL");
                    }
                    if ("FALSE".equals(cell.toString()) || "TRUE".equals(cell.toString())) {
                        cell.setCellType(CellType.NUMERIC);
                    } else {
                        cell.setCellType(CellType.STRING);
                    }
                    rowBuffer.append("'" + cell + "'" + ",");
                }
            }

            if (i == 0) {
                String titleString = titleBuffer.substring(0, titleBuffer.length() - 1);
                list.add(titleString);
                continue;
            }
            String rowString = rowBuffer.substring(0, rowBuffer.length() - 1).replaceAll("'NULL'", "NULL");
//                    .replaceAll("TRUE", "1").replaceAll("FALSE", "0");
            list.add(rowString);
        }
        return list;
    }

    /**
     * 文件是否存在
     * @param path
     */
    public static void fileExists(String path) {
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }

    }
}
